package com.humh.datasync;

/**
 * <p></p>
 * Create by hgx61 at 2024/12/22
 */

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import java.util.logging.Logger;

public class RealTimeSync {

  // 数据库连接池配置
  private static final String PROD_DB_URL = "jdbc:mysql://192.168.0.180:3306/shop_user";
  private static final String TEST_DB_URL = "jdbc:mysql://127.0.0.1:3306/shop_user";
  private static final String USER_PrOD = "root";
  private static final String PASSWORD_Prod = "123456";

  private static final String USER_Test = "root";
  private static final String PASSWORD_Test = "root";

//  private static final String SELECT_SQL = "SELECT * FROM t_stockmarketdata_v2 WHERE nDate >= ? AND nTime >= ? ORDER BY nDate, nTime";
//   + "BY nDate, nTime";
//  private static final String INSERT_SQL = "INSERT INTO t_stockmarketdata_v2 (nMarketId, sCode, sName, nDate, nTime, nStatus, uPreClose, uOpen, uHigh, uLow, uMatch, uAskPrice10, uAskVol10, uAskPrice9, uAskVol9) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  //本地测试
  private static final String SELECT_SQL = "SELECT * FROM cities";
  private static final String INSERT_SQL = "INSERT INTO cities (cityid, city, provinceid) VALUES (?, ?, ?)";

  private static final Logger logger = Logger.getLogger(RealTimeSync.class.getName());

  // HikariCP 数据库连接池
  private static HikariDataSource prodDataSource;
  private static HikariDataSource testDataSource;

  // 初始化数据库连接池
  static {
    HikariConfig prodConfig = new HikariConfig();
    prodConfig.setJdbcUrl(PROD_DB_URL);
    prodConfig.setUsername(USER_PrOD);
    prodConfig.setPassword(PASSWORD_Prod);
    prodConfig.setMaximumPoolSize(10);

    HikariConfig testConfig = new HikariConfig();
    testConfig.setJdbcUrl(TEST_DB_URL);
    testConfig.setUsername(USER_Test);
    testConfig.setPassword(PASSWORD_Test);
    testConfig.setMaximumPoolSize(10);

    prodDataSource = new HikariDataSource(prodConfig);
    testDataSource = new HikariDataSource(testConfig);
  }

  // 获取连接的方法
  private static Connection getConnection(HikariDataSource dataSource) throws SQLException {
    return dataSource.getConnection();
  }

  // 实时同步方法
  public static void syncData() {
    try (Connection prodConn = getConnection(prodDataSource);
        Connection testConn = getConnection(testDataSource)) {

      // 获取上次同步的最大 nDate 和 nTime（存储在某个表或文件中）
      long lastSyncDate = getLastSyncDate();

      // 从生产环境获取增量数据
      try (PreparedStatement stmt = prodConn.prepareStatement(SELECT_SQL)) {
//        stmt.setLong(1, lastSyncDate);
//        stmt.setLong(2, 0);  // 可能根据时间戳调整条件
        ResultSet rs = stmt.executeQuery();

        // 批量插入数据到测试环境
        try (PreparedStatement insertStmt = testConn.prepareStatement(INSERT_SQL)) {
          int count = 0;
          while (rs.next()) {
//            insertStmt.setInt(1, rs.getInt("nMarketId"));
//            insertStmt.setString(2, rs.getString("sCode"));
//            insertStmt.setString(3, rs.getString("sName"));
//            insertStmt.setLong(4, rs.getLong("nDate"));
//            insertStmt.setLong(5, rs.getLong("nTime"));
//            insertStmt.setInt(6, rs.getInt("nStatus"));
//            insertStmt.setInt(7, rs.getInt("uPreClose"));
//            insertStmt.setInt(8, rs.getInt("uOpen"));
//            insertStmt.setInt(9, rs.getInt("uHigh"));
//            insertStmt.setInt(10, rs.getInt("uLow"));
//            insertStmt.setInt(11, rs.getInt("uMatch"));
//            insertStmt.setInt(12, rs.getInt("uAskPrice10"));
//            insertStmt.setInt(13, rs.getInt("uAskVol10"));
//            insertStmt.setInt(14, rs.getInt("uAskPrice9"));
//            insertStmt.setInt(15, rs.getInt("uAskVol9"));

            insertStmt.setString(1, rs.getString("cityid"));
            insertStmt.setString(2, rs.getString("city"));
            insertStmt.setString(3, rs.getString("provinceid"));

            insertStmt.addBatch();

//            if (++count % 1000 == 0) {  // 每1000条提交一次
              insertStmt.executeBatch();
//            }
          }
//          insertStmt.executeBatch(); // 提交剩余的
        }
      }

      // 更新上次同步的时间戳
//      updateLastSyncDate(System.currentTimeMillis());
      logger.info("Data synchronized successfully!");

    } catch (SQLException e) {
      logger.severe("Error during data synchronization: " + e.getMessage());
      e.printStackTrace();
    }
  }

  // 获取最后同步的时间
  private static long getLastSyncDate() {
    // 可以从某个存储中获取最后同步的日期和时间（例如数据库表或者配置文件）
    // 这里我们假设返回一个硬编码值
    return 20231201000000L;
  }

  // 更新最后同步时间
  private static void updateLastSyncDate(long timestamp) {
    // 可以将最后同步时间存储到某个表或配置文件中
    // 这里只是一个模拟
    logger.info("Last sync time updated to: " + timestamp);
  }

  public static void main(String[] args) {
    // 设置定时任务
    ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
    scheduler.scheduleAtFixedRate(() -> syncData(), 0, 5, TimeUnit.MINUTES);
  }
}

